import sys

sys.path.append('/home/admin/app/internal_server/')
from common.log import logger
import traceback
from datetime import datetime, timedelta
from io import BytesIO

import pandas as pd
import requests
import json
import os
from db.es.es_service import ElasticsearchStorage

es = ElasticsearchStorage()

path = os.path.abspath(os.path.dirname(__file__)) + "/data/"
api_key = "dfaa22f6-7712-4ee5-ac5c-d772ead706fc"


def sendToWx(file_name_final, file_folder, api_key, mentioned_mobile_list=None):
    # 企业微信的机器人API的URL
    id_url = f'https://qyapi.weixin.qq.com/cgi-bin/webhook/upload_media?key={api_key}&type=file'
    wx_url = f'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key={api_key}'
    # 读取文件内容

    data = {'file': open(file_name_final, 'rb')}
    response = requests.post(url=id_url, files=data)
    json_res = response.json()
    media_id = json_res['media_id']
    # 构建发送文件的请求的json
    data = json.dumps(
        {"msgtype": "file", "file": {"media_id": media_id, "mentioned_mobile_list": mentioned_mobile_list}})
    # 发送请求
    r = requests.post(wx_url, data, headers={'Content-Type': 'application/json'})

    # 删除 file_dir 下的xlsx文件
    for file in os.listdir(file_folder):
        if file.endswith('.xlsx'):
            os.remove(os.path.join(file_folder, file))


def build_download_data():
    try:
        session_ids = set([])
        start_date = datetime.now().strftime('%Y-%m-%d')
        end_date = (datetime.now() + timedelta(days=1)).strftime('%Y-%m-%d')
        # 定义查询条件

        date_range_query = {
            "range": {
                "message_time": {
                    "gte": start_date,
                    "lte": end_date
                }
            }
        }
        user_session_query = {
            "query": {
                "bool": {
                    "must": [date_range_query]
                }
            },
            "size": 1000
        }

        # 执行查询
        session_results = es.search_documents('usersessions', user_session_query)

        if session_results:
            # 创建一个空的DataFrame
            df = pd.DataFrame(columns=["用户账号", "对话id", "角色", "对话内容", "对话时间", "态度", "踩的原因", "用户建议", "坐席转发"])
            # 提取查询结果
            for hit in session_results:
                session_id = hit["_source"]["session_id"]
                user_name = hit["_source"]["user_name"]
                if session_id in session_ids:
                    continue
                query = {
                    "query": {
                        "bool": {
                            "must": [
                                {
                                    "term": {
                                        "session_id": {
                                            "value": session_id
                                        }
                                    }
                                }
                            ]
                        }
                    }, "sort": [
                        {
                            "message_time": {
                                "order": "asc"
                            }
                        }
                    ]
                    , "size": 1000
                }
                results = es.search_documents('dialogues', query)
                if results:
                    # 遍历查询结果
                    for hit in results:
                        session_id = hit["_source"].get("session_id", None)
                        role = hit["_source"].get("role", None)
                        text_info = hit["_source"].get("text_info", None)
                        message_time = hit["_source"]["message_time"]
                        message_id = hit["_source"]["message_id"]
                        attitude = hit["_source"].get("attitude", None)
                        badoption = hit["_source"].get("badoption", None)
                        userPrefer = hit["_source"].get("userPrefer", None)
                        agent_transfer = "否"
                        if message_id and "agent" in message_id:
                            agent_transfer = "是"

                        df = df.append(
                            {"用户账号": user_name, "对话id": session_id, "角色": role, "对话内容": text_info,
                             "对话时间": message_time,
                             "态度": attitude,
                             "踩的原因": badoption,
                             "用户建议": userPrefer, "坐席转发": agent_transfer},
                            ignore_index=True)
                session_ids.add(session_id)

            def add_empty_row(group):
                return pd.concat([group, pd.DataFrame([[''] * len(group.columns)], columns=group.columns)],
                                 ignore_index=True)

            df = df.groupby('用户账号').apply(add_empty_row).reset_index(drop=True)

            excel_io = BytesIO()
            df.to_excel(excel_io, index=False)
            # 导出到Excel文件
            file_name = f"conversation_{start_date}.xlsx"
            # 将BytesIO的内容发送给客户端作为附件下载
            excel_io.seek(0)  # 将指针回到文件开头，确保从头开始读取
            # 将 BytesIO 对象的内容写入本地 Excel 文件
            with open(path + file_name, 'wb') as excel_file:
                excel_file.write(excel_io.getvalue())

            return path + file_name
    except Exception:
        logger.error(traceback.format_exc())


if __name__ == '__main__':
    file_name = build_download_data()
    if file_name:
        sendToWx(file_name, path, api_key)
